﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Csla;
using Csla.Data;
using System.Data;
using System.Data.SqlClient;
using ZX.SYS.Library;
using ZX.DAL.DBExtend;
using lite;
using OpenExpressApp.ORM;
namespace ZX.SYS.Bussiness
{
   [Serializable()]
    public class PhraseList    
    {
       
        #region  Factory Methods

       public static DataTable GetAllPhrase(string PClass)
        {
            return Data_Fetch(PClass);
        }
       public static DataTable GetUnPausePhrase(string PClass)
       {
           return Data_GetUnPausePhrase(PClass);
          
       }

       public static void DeletePhrase(string PClass, string PValue)
       {
            Data_DeletePhrase(PClass, PValue);
       }

       private static void Data_DeletePhrase(string PClass, string PValue)
       {
           string s = "Delete from Phrase Where Class='" + PClass + "' and PhraseValue='" + PValue + "'";
           int i = 0;
           try
           {
               i = new DbSql().ExecuteSql(s);
               if (i == 0) { throw new Exception("Delete from Phrase Error"); }
           }
           catch (Exception ex)
           { throw ex; }
       }
       public static void UpDatePhrase(string PClass, string PValue, string PText, bool Pause)
       {
            Data_UpDatePhrase(PClass, PValue, PText, Pause);
       }

       private static void Data_UpDatePhrase(string PClass, string PValue, string PText, bool Pause)
       {
           string sUpdate = "Update Phrase set Class='" + PClass + "',PhraseValue='" + PValue + "'";
           sUpdate += ",PhraseText='" + PText + "',Pause=" + Convert.ToInt32(Pause);
           sUpdate += "WHERE Class='" + PClass + "' AND PhraseValue='" + PValue + "'";
           string sInsert = "Insert into Phrase (Class,PhraseValue,PhraseText,Pause) ";
           sInsert += " values ('" + PClass + "','" + PValue + "','" + PText + "'," + Convert.ToInt32(Pause) + ")";

           int i = 0;
           try
           {
               i = new DbSql().ExecuteSql(sUpdate);
               if (i == 0)
               {
                   string s = "Insert into Phrase (Class,PhraseValue,PhraseText,Pause) ";
                   s += " values ('" + PClass + "','" + PValue + "','" + PText + "'," + Convert.ToInt32(Pause) + ")";
                   i =new DbSql().ExecuteSql(sInsert);
                   if (i == 0) { throw new Exception("Update from Phrase Error"); }
               }
           }
           catch (Exception ex)
           { throw ex; }
       }

       public static void UpDatePhrase(string PClass, string PValue, string PText, bool Pause, int AddFlag, string lblValue)
       {
           Data_UpDatePhrase(PClass, PValue, PText, Pause, AddFlag,lblValue);
       }

       private static void Data_UpDatePhrase(string PClass, string PValue, string PText, bool Pause, int AddFlag, string lblValue)
       {
           string sUpdate = "Update Phrase set Class='" + PClass + "',PhraseValue='" + PValue + "'";
           sUpdate += ",PhraseText='" + PText + "',Pause=" + Convert.ToInt32(Pause);
           sUpdate += " WHERE Class='" + PClass + "' AND PhraseValue='" + lblValue + "'";
           string sInsert = "Insert into Phrase (Class,PValue,PhraseText,Pause) ";
           sInsert += " values ('" + PClass + "','" + PValue + "','" + PText + "'," + Convert.ToInt32(Pause) + ")";

           int i = 0;
           try
           {
               if (AddFlag == 1)
               {
                   if (Exist(PValue, PClass))
                   {
                       throw new Exception("已经存在此索引的相关信息,不能新增插入!");
                   }

                   string s = "Insert into Phrase (Class,PhraseValue,PhraseText,Pause) ";
                   s += " values ('" + PClass + "','" + PValue + "','" + PText + "'," + Convert.ToInt32(Pause) + ")";
                   i = new DbSql().ExecuteSql(sInsert);
                   if (i == 0) { throw new Exception("Update from Phrase Error"); }
               }

               else
               {
                   if (Exist(PValue, PClass) && !(lblValue == PValue))
                   {
                       throw new Exception("已经存在此索引的相关信息,不能修改!");
                   }

                   i = new DbSql().ExecuteSql(sUpdate);
               }
           }
           catch (Exception ex)
           {
               throw ex;
           }
       }

       public static bool Exist(string PValue, string PClass)
       {
           return Data_Exist(PValue, PClass);
       }

       private static bool Data_Exist(string PValue, string PClass)
       {
           string swhere = "SELECT * FROM Phrase WHERE  Class='" + PClass + "' AND PhraseValue='" + PValue + "'";
            DataTable dt=new DbSql().ExecuteQuery(swhere);
            return (dt.Rows[0] != null);
       }

        private PhraseList()
        {     
             
        }

   

        #endregion

        #region  Data Access
        [Serializable()]
        private class Criteria
        {
            private string _Class;
            /// <summary>
            /// 类型
            /// </summary>
            public string Class
            {
                get { return _Class; }
            }
            private string _PhraseValue;
            /// <summary>
            /// 片语值
            /// </summary>
            public string PhraseValue
            {
                get { return _PhraseValue; }
            }

            public Criteria(string PClass,string PValue)
            { 
                _Class = PClass;
                _PhraseValue = PValue;
            }
        }
        private static DataTable Data_Fetch(string PClass)
        {
 
            string s = " SELECT * FROM Phrase WHERE class ='" + PClass + "'";
            return new DbSql().ExecuteQuery(s);

        }
        private static DataTable Data_GetUnPausePhrase(string PClass)
        {
            string s = " SELECT P.*,PhraseValue as LocationID,PhraseText as LocationName,";
            s += " (case when PhraseValue is null then PhraseText else PhraseValue+(case when PhraseText is null then '' else '.'+PhraseText end) end) AS LocationFullName";
            s += " FROM Phrase P WHERE class ='" + PClass + "'";
            s += " AND P.Pause=0";
            return new DbSql().ExecuteQuery(s);
        }
        
        #endregion
    }
}
